import pymysql
from todo import Todo


class TodoManagerWithMysql:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='10.211.55.3', database='test0825', user='root', password='root@123ABC',
                                  autocommit=True)

    def create(self, title: str):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('insert into todos (title) value(%s)', (title,))
            s.execute('select last_insert_id()')
            (id,) = s.fetchone()
            return Todo(title, id=id)

    def init_db(self):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('''
            create table if not exists todos(
            id int primary key not null auto_increment,
            title varchar(50) not null,
            completed int default 0
            );
            ''')

    def convent_row_to_object(self, i):
        return Todo(i[1], id=i[0], completed=True if i[2] == 1 else False)

    def get_list(self):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('select * from todos')
            data = s.fetchall()
            list_todos = []
            for i in data:
                list_todos.append(self.convent_row_to_object(i))
            return list_todos

    def get_id(self, id):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('select * from todos')
            for i in self.get_list():
                if i.id == id:
                    return i

    def mark_completed(self, id):
        todo = self.get_id(id)
        if todo is not None:
            todo.completed = True
            with self.get_connection() as c:
                s = c.cursor()
                s.execute('UPDATE todos SET completed = 1 WHERE id =(%s)', (id,))
                return todo

    def del_todos(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where id=%s', (id,))
            s = self.get_id(id)
            if s is not None:
                cursor.execute('delete from todos where id=%s', (id,))
                return s
